﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NTTDATA.BEENChina.ResultCertification.Checker;
using NTTDATA.BEENChina.ResultCertification.Config;
using NTTDATA.BEENChina.ResultCertification.model;
using Microsoft.ApplicationBlocks.Data;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
    public class VerfityAccess:IVerfity
    {
        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["ResultCertificationDB"];
            }
        }

        public int AddVerfityItem(VerfityItem verfityItem)
        {
            if (verfityItem == null)
                return -1;

            string sql =    "  INSERT INTO [VerfityItem] "+
                           "    ([VerfityItemID]			,[VerfityItemName]  "+
                           "    ,[VerfityItemClassName]	,[VerfityItemInstance]  "+
                           "    ,[ModelItem]	,[CategoryType]       ,[Memo])  "+
                           "    VALUES    "+
                           "    (@VerfityItemID           ,@VerfityItemName "+
                           "    ,@VerfityItemClassName    ,@VerfityItemInstance "+
                           "    ,@ModelItem  ,@CategoryType        ,@Memo)";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@VerfityItemID",  verfityItem.VerfityItemId),
                    new SqlParameter("@VerfityItemName",  verfityItem.VerfityItemName),
                    new SqlParameter("@VerfityItemClassName",  verfityItem.VerfityItemClassName),
                    new SqlParameter("@VerfityItemInstance",    verfityItem.VerfityItemInstance),
                    new SqlParameter("@ModelItem",  verfityItem.IsModelItem),
                    new SqlParameter("@CategoryType",  verfityItem.CategoryType),
                    new SqlParameter("@Memo",  verfityItem.Memo)
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql,sqlParameters);

        }

        public int UpdateVerfityItem(VerfityItem verfityItem)
        {
            if (verfityItem == null)
                return -1;

            string sql = " UPDATE  [VerfityItem] "+
                         "  SET [VerfityItemName] = @VerfityItemName"+
                             "  ,[VerfityItemClassName] = @VerfityItemClassName"+
                             "  ,[VerfityItemInstance] = @VerfityItemInstance"+
                             "  ,[ModelItem] = @ModelItem"+
                             "   ,[CategoryType] = @CategoryType"+
                             "  ,[Memo] = @Memo " +
                             //"  ,[Selected] = @Selected " +
                         "  WHERE [VerfityItemID] = @VerfityItemID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@VerfityItemID",  verfityItem.VerfityItemId),
                    new SqlParameter("@VerfityItemName",  verfityItem.VerfityItemName),
                    new SqlParameter("@VerfityItemClassName",  verfityItem.VerfityItemClassName),
                    new SqlParameter("@VerfityItemInstance",    verfityItem.VerfityItemInstance),
                    new SqlParameter("@ModelItem",  verfityItem.IsModelItem),
                    new SqlParameter("@CategoryType",  verfityItem.CategoryType),
                    new SqlParameter("@Memo",  verfityItem.Memo),
                    //new SqlParameter("@Selected",  verfityItem.Selected),
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);
        } 

        public int UpdateSelectVerfityItem(string verfityItemId,bool selected)
        {
            if (string.IsNullOrEmpty(verfityItemId) )

                return -1;

            string sql = " UPDATE  [VerfityItem] " +
                         "  SET   [Selected] = @Selected " +
                         "  WHERE [VerfityItemID] = @VerfityItemID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@VerfityItemID",   verfityItemId), 
                    new SqlParameter("@Selected",  selected),
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);
        }

        public int RemoveVerfityItem(string verfityItemId)
        {
            string sql = " DELETE FROM [VerfityItem] WHERE [VerfityItemID] = @VerfityItemID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@VerfityItemID",  verfityItemId)
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql,sqlParameters);
        }

        public VerfityItem GetVerfityItem(string verfityItemId)
        {
            string sql = " SELECT  [VerfityItemID],[VerfityItemName],[VerfityItemClassName]   "+
                        "          ,[VerfityItemInstance],[ModelItem],[CategoryType],[Memo],[selected] " +
                        " FROM [VerfityItem] "+
                        " WHERE [VerfityItemID] = @VerfityItemID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@VerfityItemID",  verfityItemId)
                };
            List<VerfityItem> items = GetVerfityItems(sql, sqlParameters);
            return items != null && items.Count > 0 ? items[items.Count - 1] : null;

        }

        public List<VerfityItem> GetVerfityItems(EnumCategoryType categoryType)
        {
            string sql = " SELECT  [VerfityItemID],[VerfityItemName],[VerfityItemClassName]   " +
                         "          ,[VerfityItemInstance],[ModelItem],[CategoryType],[Memo],[selected]  " +
                         " FROM [VerfityItem] " +
                         " WHERE [CategoryType] = @CategoryType ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@CategoryType",  (int)categoryType)
                };
            return GetVerfityItems(sql, sqlParameters);

            /*
            List<VerfityItem> verfityItems = null;
            try
            {
                using (SqlDataReader reader = (new SqlHelper()).ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (verfityItems == null)
                            verfityItems = new List<VerfityItem>();

                        VerfityItem verfityItem = new VerfityItem();

                        verfityItem.VerfityItemId = reader["VerfityItemID"].ToString();
                        verfityItem.VerfityItemName = reader["VerfityItemName"].ToString();
                        verfityItem.VerfityItemClassName = reader["VerfityItemClassName"].ToString();
                        verfityItem.VerfityItemInstance = reader["VerfityItemInstance"].ToString();
                        verfityItem.IsModelItem = int.Parse(reader["ModelItem"].ToString()) >0 ;
                        verfityItem.CategoryType = (EnumCategoryType)int.Parse(reader["CategoryType"].ToString());
                        verfityItem.Memo = reader["Memo"].ToString();
                        verfityItem.Selected = (bool)reader["selected"];

                        verfityItems.Add(verfityItem);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("VerfityAccess.GetVerfityItems", ex);
            }

            return verfityItems;
             */
        }

        public List<VerfityItem> GetVerfityItems()
        {
            string sql = " SELECT  [VerfityItemID],[VerfityItemName],[VerfityItemClassName]   " +
             "          ,[VerfityItemInstance],[ModelItem],[CategoryType],[Memo],[selected]  " +
             " FROM [VerfityItem] ";             

            return GetVerfityItems(sql, null);
        }

        private List<VerfityItem> GetVerfityItems(string sql, SqlParameter[] sqlParameters)
        { 
            List<VerfityItem> items = new List<VerfityItem>();
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        VerfityItem verfityItem = new VerfityItem();

                        verfityItem.VerfityItemId = reader["VerfityItemID"].ToString();
                        verfityItem.VerfityItemName = reader["VerfityItemName"].ToString();
                        verfityItem.VerfityItemClassName = reader["VerfityItemClassName"].ToString();
                        verfityItem.VerfityItemInstance = reader["VerfityItemInstance"].ToString();
                        verfityItem.IsModelItem = int.Parse(reader["ModelItem"].ToString()) > 0;
                        verfityItem.CategoryType = (EnumCategoryType)int.Parse(reader["CategoryType"].ToString());
                        verfityItem.Memo = reader["Memo"].ToString();
                        verfityItem.Selected = reader["selected"] != DBNull.Value && (bool)reader["selected"];

                        items.Add(verfityItem);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("VerfityAccess.GetVerfityItems", ex);
            }

            return items;
        }

        public bool VerfityItemId(string verfityItemId)
        {
            string sql = " SELECT  COUNT(*) " +
                         " FROM [VerfityItem] " +
                        " WHERE [VerfityItemID] = @VerfityItemID ";
            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@VerfityItemID",  verfityItemId)
                };

            string count =  SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, sql, sqlParameters).ToString();

            return int.Parse(count) > 0;
        }

        public List<string> GetVerfityCategorys()
        {
            string sql = " select Distinct [CategoryID] "+
                         " from CategoryVerfityItem A  "+
                         "		inner join VerfityItem B on "+
                         "		A.VerfityItemID = B.VerfityItemID "+
                         "  where	B.Selected = 1 ";

            List<string> verfityCategorys = null;

            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql ))
                {
                    while (reader.Read())
                    {
                        if ( verfityCategorys == null)
                            verfityCategorys = new List<string>();

                        verfityCategorys.Add(reader["CategoryID"].ToString());
                        
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("VerfityAccess.GetVerfityCategorys", ex);
            }

            return verfityCategorys;
        }

        public List<Verfity> GetSelectedVerfitys(string categoryId)
        {
            string sql = " SELECT VerfityItemInstance   FROM CategoryVerfityItem A   "+
                         "		INNER JOIN VerfityItem B ON  "+
                         "		A.VerfityItemID = B.VerfityItemID  "+
                         " WHERE	B.Selected = 1 and CategoryID = @CategoryID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@CategoryID",  categoryId)
                };

            Util.Serializer serializer = new Util.Serializer();
            List<Verfity> verfities = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {

                        if (verfities == null)
                            verfities = new List<Verfity>();

                        string verfityInstance = reader["VerfityItemInstance"].ToString();

                       

                        if (!String.IsNullOrEmpty(verfityInstance))
                        {
                           Verfity verfity = serializer.DeSerializeXml(typeof(Verfity), verfityInstance) as Verfity;

                            verfities.Add(verfity);
                        }

                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("VerfityAccess.GetSelectedVerfitys Categoryid:" + categoryId, ex);
            }

            return verfities;
        }


        public Verfity GetSelectedVerfityForTest(string categoryId,string verfityItemID)
        {
            string sql = " SELECT VerfityItemInstance   FROM CategoryVerfityItem A   " +
                         "		INNER JOIN VerfityItem B ON  " +
                         "		A.VerfityItemID = B.VerfityItemID  " +
                         " WHERE	 CategoryID = @CategoryID and A.[VerfityItemID] = @VerfityItemID";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@CategoryID",  categoryId),
                    new SqlParameter("@VerfityItemID",  verfityItemID),

                };

            Util.Serializer serializer = new Util.Serializer();
            Verfity verfity = null;

            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {

                         

                        string verfityInstance = reader["VerfityItemInstance"].ToString();

                        

                        if (!String.IsNullOrEmpty(verfityInstance))
                        {
                              verfity = serializer.DeSerializeXml(typeof(Verfity), verfityInstance) as Verfity;

                            
                        }

                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("VerfityAccess.GetSelectedVerfity", ex);
            }

            return verfity;
        }
    }
}
